<?php
/**
 * Bulk Move TapeStore
 *
 * Move multiple TapeStore records and return the ones that don't exist in the database
 *
 * @author      Adam Lewis      <adam.lewis@bestservedcold.com>
 * @since       2012-04-16
 * @param       string          $location
 * @param       array           $spools
 * @return      mixed           // Returns either an array of spool ids that don't exist or FALSE
 */
function bulk_move_tapestore($location, $spools)
{
    foreach($spools as $spool_number)
    {
        // Firstly, check if the spool number exists
        if ($spools = check_spool_exists($spool_number, TRUE))
        {
            foreach ($spools as $spool)
            {
                move_spool($spool->id, $location);
            }
        }
        elseif ($spool_number != "")
        {
            $spool_ids[] = $spool_number;
        }
    }
    return (!isset($spool_ids) || empty($spool_ids)) ? FALSE : $spool_ids;
}

/**
 * Move Spool
 *
 * Move a spool number's location
 *
 * @author      Adam Lewis      <adam.lewis@bestservedcold.com>
 * @since       2012-04-16
 * @param       $id
 * @param       $location
 * @return      bool
 */
function move_spool($id, $location)
{
    return crud_save('infax', array('current_location' => $location, 'id'=>$id));
}

/**
 * Check Spool Exists
 *
 * Does the spool number exist or not?  Who knows?  Well this function does.  Returns a list of records if there is
 * more than one record per spool number
 *
 * @author      Adam Lewis      <adam.lewis@bestservedcold.com>
 * @since       2012-04-16
 * @param       $spool_number
 * @return      mixed           // either returns the spool or FALSE
 *
 */
function check_spool_exists($spool_number = FALSE, $multiple = FALSE)
{
    if (!$spool_number || $spool_number == '')
    {
        return FALSE;
    }
    else
    {
        if ($multiple)
        {
            return crud_find_multiple('infax', array('spool_number' => trim($spool_number)));
        } else {
            return crud_find('infax', array('spool_number' => trim($spool_number)));
        }
    }
}

/**
 * Get TapeStore Record
 *
 * Simple function, it just goes and get's the record with the database id, easy really.  Here are some translation
 * notes which might be useful, this is the old database table compared to the new one so that we know what each field
 * reference matches the old data.
 *
 * Translation Notes
 *
 * ix_media.id              -> infax.id
 * ix_media.spool_num       -> infax.spool_number
 * ix_media.status          -> infax.edit_code?
 * ix_media.type            -> infax.rec_status
 * ix_media.duration        -> infax.duration
 * ix_media.location        -> infax.current_location
 * ix_media.media_desc      -> infax.tech_code
 * ix_media.descrpition     -> infax.description
 * ix_media.fkey_prog_id    -> infax.fkey_program (All NULL's in the new table?)
 *
 * @author      Adam Lewis      <adam.lewis@bestservedcold.com>
 * @since       2012-04-16
 * @param       integer         $tapestoreId
 * @return      array           recordset
 */
function get_tapestore_record($tapestoreId)
{
    return crud_get('infax', $tapestoreId);
}

/**
 * Handle TapeStore Record
 *
 * Saves the updated TapeStore record, most probably from a form.
 *
 * @author      Adam Lewis      <adam.lewis@bestservedcold.com>
 * @since       2012-04-16
 * @param       array           $record
 * @return      mixed
 */
function handle_tapestore_record($record)
{
    // Firstly, we need to clean up a couple of fields
    $record['edit_code']       = '/'.$record['edit_code'];
    $record['review_status']   = ($record['review_status'] == 1) ? 'WIPED' : '';
    return crud_save('infax', $record);
}

/**
 * Get NID from ProgNumber
 *
 * Returns the NID from the programe number
 *
 * @author      Adam Lewis      <adam.lewis@bestservedcold.com>
 * @since       2012-04-16
 * @param       integer         $prog_number
 * @return      array           record
 */
function get_nid_from_prognumber($prog_number)
{
    return crud_find('videofinder_program', array('prog_number' => $prog_number))->nid;
}

/**
 * Delete TapeStore record
 *
 * Does what it says on the tin really
 *
 * @author      Adam Lewis      <adam.lewis@bestservedcold.com>
 * @since       2012-04-16
 * @param       integer         $tapestoreId
 * @return      void
 */
function delete_tapestore_record($tapestoreId)
{
    crud_delete('infax', $tapestoreId);
}

function crud_save($table, $data, $debug = FALSE)
{
  $crud = _crud_field_data($table);
  $data = (object)$data;

  $key = $crud->pkey;
  // Temporary fix.
  if (!$key || (!$crud->auto_increment && !$crud->sequence && !$data->$key)) {
    drupal_set_message(t('CRUD: Invalid table / no key found (%table)', array('%table' => $table)), 'error');
    return false;
  }
  elseif ($crud->auto_increment) {
    $new_record = !isset($data->$key);
  }
  else {
    if ($crud->sequence && !$data->$key) {
      $data->$key = db_next_id($table .'_'. $key);
      $new_record = TRUE;
    }
    else {
      $new_record = !db_result(db_query('SELECT count(*) FROM {'. $table .'} WHERE %s = %d', $key, $data->$key));
    }
  }
  $fields = $crud->fields;

  $save_fields = array();
  $save_params = array();
  if ($new_record && !$data->created_on) {
    $data->created_on = '|null|';
  }
  // Unset updated_on, if it exists...
  unset($data->updated_on);

  foreach ($fields as $field => $fmt) {
    if (strstr($fmt, '%') === FALSE) {
      $save_fields[$field] = $fmt;
    }
    elseif (isset($data->$field)) {
      if ($data->$field === "|null|") {
        $save_fields[$field] = 'null';
      }
      else {
        $save_fields[$field] = $fmt;
        $value = $data->$field;
        if (is_array($value) || is_object($value)) {
          $value = serialize($value);
        }
        $save_params[$field] = $value;
      }
    }
  }
  if (!($save_fields && $save_params)) {
    return FALSE;
  }
  if (!$new_record) {
    foreach ($save_fields as $field => $fmt) {
      if (!($field == 'created_on' && $fmt == 'null')) {
        $update_fields[] = $field .'='. $fmt;
      }
    }

    $save_params[] = $key;
    $save_params[] = $data->$key;
    $sql = 'UPDATE {'. $table .'} SET '. implode(',', $update_fields) .' WHERE %s = %d';
    db_query($sql, $save_params);
    $id = $data->$key;
    // build update statement
  }
  else {
    if (!$crud->auto_increment) {
      $save_fields[$key] = "'%s'";
      $save_params[] = $data->$key;
    }

    // build insert statement
    $sql = 'INSERT INTO {'. $table .'} ('. implode(',', array_keys($save_fields)) .') values ('. implode(',', $save_fields) .')';
    db_query($sql, $save_params);
    if (!$crud->sequence) {
      $id = mysql_insert_id();
    }
  }

  if ($debug) {
    print theme('crud_debug', $sql, $save_params, mysql_error());
  }
  return $id;
}

function theme_crud_debug($sql, $params, $error) {
  $save_params = print_r($params, 1);
  $output = <<<HTML
    <br />SQL Statement:<br />
    $sql<br />
    <br />
    <br />
    Params:<br />
    <pre>
      $save_params
    </pre>
    <br />
    <br />
    Errors:<br />
    $error<br />
    <br />
HTML;
  return $output;
}

function crud_delete($table, $key) {
  $crud = _crud_field_data($table);
  db_query('DELETE FROM {'. $table .'} WHERE %s = \'%s\'', $crud->pkey, $key);
}

function crud_get($table, $key) {
  $crud = _crud_field_data($table);
  return db_fetch_object(db_query('SELECT * FROM {'. $table .'} WHERE %s = \'%s\'', $crud->pkey, $key));
}

function crud_get_like($table, $expression=FALSE, $field = FALSE)
{
    $matches = array();
    $sql = 'SELECT '.$field.' FROM {'.$table.'} WHERE '.$field.' LIKE "'.$expression.'%" GROUP BY '.$field.' LIMIT 0,10';
    $result = db_query($sql);
	while ($row = db_fetch_object($result))
    {
    	$matches[$row->$field] = $row->$field;
    }
    return drupal_json($matches);
}

function crud_find($table, $data, $debug = FALSE) {
  $data = (array)$data;

  $crud = _crud_field_data($table);
  $where = array();
  $params = array();
  foreach ($crud->fields as $field => $format) {
    if (isset($data[$field])) {
      $where[] = $field .' = '. $format;
      $params[] = $data[$field];
    }
  }
  $ret = null;
  if ($where) {
    $sql = 'SELECT * FROM {'. $table .'} WHERE '. implode(' AND ', $where) .' LIMIT 1';
    $ret = db_fetch_object(db_query($sql, $params));
    if (!is_object($ret)) {
      $ret = null;
    }
  }
  if ($debug) {
    print 'SQL Statement:<br />'. $sql .'<br />Data:<br /><pre>'. print_r($ret, TRUE) .'Parameters:'. print_r($params, TRUE) .'</pre>';
  }
  return $ret;
}

function crud_find_multiple($table, $data, $options=array(), $debug = FALSE) {
  $data = (array)$data;
  $items = array();
  $crud = _crud_field_data($table);
  $where = array();
  $params = array();
  foreach ($crud->fields as $field => $format) {
    if (isset($data[$field])) {
      $where[] = $field .' = '. $format;
      $params[] = $data[$field];
    }
  }
  $ret = null;
  if ($where) {
    $sql = 'SELECT * FROM {'. $table .'} WHERE '. implode(' AND ', $where) .' ';

    if (isset($options['order'])) {
      $sql .= "ORDER BY {$options['order']} ";
    }

    $res = db_query($sql, $params);
    $pkey = $crud->pkey;
    while ($item = db_fetch_object($res)) {
      $items[$item->$pkey] = $item;
    }
  }
  if ($debug) {
    print 'SQL Statement:<br />'. $sql .'<br />Data:<br /><pre>'. print_r($ret, TRUE) .'Parameters:'. print_r($params, TRUE) .'</pre>';
  }
  return $items;
}

function _crud_field_data($table) {
  $cache_key = 'crud.'. $table;
  if ($crud = cache_get($cache_key, 'cache')) {
    return unserialize($crud->data);
  }

  $fref = db_query('SHOW COLUMNS FROM {'. $table .'}');
  $crud = new stdClass();
  $crud->pkey = null;
  $crud->sequence = FALSE;
  $crud->fields = array();
  $crud->auto_increment = FALSE;

  while ($field = db_fetch_object($fref)) {
    if ($field->Extra == 'auto_increment') {
      $crud->pkey = $field->Field;
      $crud->auto_increment = TRUE;
    }
    elseif (db_result(db_query('SELECT 1 FROM {sequences} WHERE name = "%s"', $table .'_'. $field->Field))) {
      $crud->pkey = $field->Field;
      $crud->sequence = TRUE;
    }
    else {
      if ($field->Key == 'PRI') {
        $crud->pkey = $field->Field;
      }

      preg_match('/^[^\(]*/', $field->Type, $matches);
      $type = $matches[0];

      switch ($type) {
        case 'float':
        case 'double precision':
        case 'real':
        case 'numeric':
        case 'decimal':
          $crud->fields[$field->Field] = '%f';
          break;
        case 'int':
        case 'integer':
        case 'tinyint':
        case 'smallint':
        case 'mediumint':
        case 'bigint':

          $crud->fields[$field->Field] = '%d';
          break;
        default:
          $crud->fields[$field->Field] = '"%s"';
      }
    }
  }
  cache_set($cache_key, 'cache', serialize($crud));
  return $crud;
}

function crud_simpletest() {
  $module_name = 'crud';
  $dir = drupal_get_path('module', $module_name) .'/tests';
  $tests = file_scan_directory($dir, '\.test$');
  return array_keys($tests);
}